
package com.compuvest.webstat;

// <editor-fold defaultstate="collapsed" desc="import">
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

import javax.naming.Context;
import javax.naming.InitialContext;

import java.io.IOException;

import java.util.Map;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
// </editor-fold>

/**
 *http://www.quirksmode.org/js/detect.html
 * 
 * BROWSER
  {ident:"Explorer",    nm:1},
  {ident:"Firefox",     nm:2},
  {ident:"Chrome",      nm:3},
  {ident:"OmniWeb",     nm:4},
  {ident:"Safari",      nm:5},
  {ident:"Opera",       nm:6},
  {ident:"iCab",        nm:7},
  {ident:"Konqueror",   nm:8},
  {ident: "Camino",     nm:9},
  {ident:"Netscape",    nm:10},
  {ident: "Mozilla",    nm:11},
 *
 * OS
  {ident:"Windows",     nm:1},
  {ident:"Linux",       nm:2},
  {ident:"Mac",         nm:3},
  {ident:"iPod",        nm:4}
 *
 * @author vasil
 */
public class Sniffer extends HttpServlet {

    private static final Logger logger = Logger.getLogger(Sniffer.class.getName());

    private DataSource ds;
    private Map<String,String> advertTable;

// <editor-fold defaultstate="collapsed" desc="init()">

    @Override
    public void init(ServletConfig config) throws ServletException {
        super.init(config);

        try {
            Context initCtx = new InitialContext();
            ds = (DataSource)initCtx.lookup("jdbc/mysql");
        }
        catch(Exception e) {
            logger.log(Level.SEVERE, "init()", e);
        }

        // fill advert table fro convert name to id
        advertTable = getAdvertTable();

        logger.log(Level.INFO, "version = {0}", getVersion());
     }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="processRequest(HttpServletRequest request, HttpServletResponse response)">

    protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        response.setContentType("text/html;charset=UTF-8");
/*
        System.out.println("Sniffer:"
                // main params
                + "\nwebid=" + request.getParameter("wid")  // web site id (req) def=0
                + "\naid=" + request.getParameter("aid")    // advert id (req) def=''
                + ":" + (advertTable.containsKey(request.getParameter("aid")) ? advertTable.get(request.getParameter("aid")) : "1")
                + "\nsrc=" + request.getParameter("src")    // Search= (req) def=''
                + "\nsku=" + request.getParameter("sku")    // accwareSKU (req) def=''
                // additions params
                + "\ncvip=" + request.getParameter("cvip")  // ip address (integer format)
                + "\npg=" + request.getParameter("pg")      // count of page
                + "\nrf=" + request.getParameter("rf")      // comming from
                + "\nurl=" + request.getParameter("url")    // current url
                + "\npage=" + request.getParameter("page")  // current page
                + "\nbws=" + request.getParameter("bws")    // browser info
        );
*/
        updateMfrNumberStat(
            request.getParameter("wid"),
            (advertTable.containsKey(request.getParameter("aid")) ? advertTable.get(request.getParameter("aid")) : "1"),
            request.getParameter("src"),
            request.getParameter("sku")
        );
/*
        setStat(request.getParameter("id"),
                    request.getParameter("wid"),
                    request.getParameter("cvip"),
                    request.getParameter("sid"),
                    request.getParameter("nav"),
                    request.getParameter("ver"),
                    request.getParameter("os"),
                    request.getParameter("pg"),
                    request.getParameter("rf"),
                    request.getParameter("url"),
                    request.getParameter("srch"),
                    request.getParameter("adv"),
                    request.getParameter("page")
                    );
*/
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getVersion()">

    private String getVersion() {

        String result = "Error";
        try(
            Connection conn = ds.getConnection();
            PreparedStatement ps = conn.prepareStatement("SELECT VERSION() version");
            ResultSet rs = ps.executeQuery()
         ) {
            if(rs.next()) {
                result = rs.getString("version");
            }
            rs.close();
            ps.close();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getVersion()", e);
        }

        return result;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getAdvert()">

    private Map<String,String> getAdvertTable() {

        Map<String,String> adverts = new HashMap<>();

        try(
            Connection conn = ds.getConnection();
            PreparedStatement ps = conn.prepareStatement("SELECT advert_id,name FROM sklad.advert");
            ResultSet rs = ps.executeQuery()
        ) {
            while(rs.next()) {
                adverts.put(rs.getString("name"), rs.getString("advert_id"));
            }
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "getAdvertTable()", e);
        }

        return adverts;
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="setStat()">

    private void setStat(
        String unixTime,// uniq id for refresh IE
        String webid,   // website uniq ID
        String cvip,    // ip address
        String sid,     // session id
        String nav,     // navigator
        String ver,     // navigator version
        String os,      // o system
        String pg,      // how many visit to pages
        String rf,      // reference
        String url,     // full website URL
        String srch,    // come from advert site what serch ?
        String adv,     // come from advert site
        String page)    // shot URL (like /Search.jsp  or /index.jsp or /
    {

        try(
            Connection conn = ds.getConnection();
            CallableStatement cs = conn.prepareCall("{ call webstat.setStat(?,?,?,?,?,?,?,?,?,?,?,?) }")
        ) {
            cs.setString(1, webid);
            cs.setString(2, cvip);
            cs.setString(3, sid);
            cs.setString(4, nav);
            cs.setString(5, ver);
            cs.setString(6, os);
            cs.setString(7, pg);
            cs.setString(8, rf);
            cs.setString(9, url);
            cs.setString(10, srch);
            cs.setString(11, adv);
            cs.setString(12, page);

            cs.executeUpdate();
        }
        catch(SQLException e) {
            logger.log(Level.SEVERE, "setStat()", e);
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="updateMfrNumberStat(String clientID, String advertName, String mfrNumber, String accwareSKU)">

    private void updateMfrNumberStat(String webID, String advertID, String mfrNumber, String accwareSKU) {

        if(webID != null && webID.length() > 0 && mfrNumber != null && mfrNumber.length() > 0 ) {

            try(
                Connection conn = ds.getConnection();
                CallableStatement cs = conn.prepareCall("{ call webstat.setMfrNumberStat(?,?,?,?) }")
            ) {
                cs.setString(1, webID);
                cs.setString(2, advertID);
                cs.setString(3, mfrNumber);
                cs.setString(4, accwareSKU);

                cs.executeUpdate();
            }
            catch(SQLException e) {
                logger.log(Level.SEVERE, "updateMfrNumberStat()", e);
            }
        }
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="HttpServlet methods.">

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)   throws ServletException, IOException {
        processRequest(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)  throws ServletException, IOException {
        processRequest(request, response);
    }

    @Override
    public String getServletInfo() {
        return "Sniffer";
    }

    @Override
    public void destroy() {
        super.destroy();

        ds = null;

    }
// </editor-fold>

}
